A database join operation combines records from more than one database table. A join essentially creates a set that can be saved as its own independent database table. There are a variety of types of joins.
One type of join is called a nested join. A nested join is an efficient join method that, depending on conditions of join query, may be applicable when one of the tables of a join operation has a secondary index or a unique primary index. One flavor of nested join is a remote nested join, which has two key characteristics. First, a remote nested join does not require the other table to be redistributed or duplicated. Instead, a message is sent from one Access Module Processor (AMP) to another AMP (hence named “remote”) to get the matching rows from the other table. Second, a remote nested join can carry out the join operation in a single-AMP or two-AMP step process. Accordingly, a remote nested join reduces the total number of AMPs participating in processing the join query. These two characteristics encourage query optimizers to select remote nested joins as the most efficient join techniques when available.
However, the availability of remote nested joins may be limited for a particular query or type of tables associated with the particular query. Therefore, usage in Relational Database Management Systems (RDBMSs) may be limited or restricted.
Therefore, there is a need to improve usage and availability of remote nested joins in RDBMSs.